package com.nwpu.coursesupport.junit.excel;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.nwpu.coursesupport.entity.Resource;
import com.nwpu.coursesupport.enumeration.FileType;

public class ExcelReader {
	
	public static List<List<String>> parseExcel(Resource r) {
		if (r == null) {
			return null;
		}
		try {
			if (r.getFileType() == FileType.XLS) {
				return readXls(r.getUrl());
			} else if (r.getFileType() == FileType.XLSX) {
				return readXlsx(r.getUrl());
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public static void main(String[] args) throws Exception {
		String path="C:\\Users\\Administrator\\Desktop\\1.xlsx";
		List<List<String>> list=null;
		if(path.endsWith("xls")){
			list=readXls(path);
		}else if(path.endsWith("xlsx")){
			list=readXlsx(path);
		}
		if(list != null){
			for(List<String> l : list){
				for(String s : l){
					System.out.print(s+" ");
				}
				System.out.println();
			}
		}
	}
	
	
	public static List<List<String>> readXls(String path) throws Exception{
		InputStream is =new FileInputStream(path);
		HSSFWorkbook hssfWorkbook=new HSSFWorkbook(is);
		List<List<String>> result=new ArrayList<List<String>>();
		
		for(int numSheet =0; numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){
			HSSFSheet hssfSheet= hssfWorkbook.getSheetAt(numSheet);
			if(hssfSheet == null){
				continue;
			}
			for(int rowNum = 1 ;rowNum <= hssfSheet.getLastRowNum();rowNum++ ){
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				
				int minColIx = hssfRow.getFirstCellNum();
				int maColIx = hssfRow.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				
				for(int colIx = minColIx;colIx<maColIx;colIx++){
					HSSFCell cell = hssfRow.getCell(colIx);
					if(cell == null){
						continue;
					}
					rowList.add(getStringVal(cell));
				}
				result.add(rowList);
			}
			
		}
		return result;
	}
	
	public static List<List<String>> readXlsx(String path) throws Exception{
		InputStream is =new FileInputStream(path);
		@SuppressWarnings("resource")
		XSSFWorkbook xssfWorkbook=new XSSFWorkbook(is);
		List<List<String>> result=new ArrayList<List<String>>();
		
		for(Sheet xssfSheet : xssfWorkbook){
			if(xssfSheet == null){
				continue;
			}
			for(int rowNum = 1 ;rowNum <= xssfSheet.getLastRowNum();rowNum++ ){
				Row row = xssfSheet.getRow(rowNum);
				
				int minColIx = row.getFirstCellNum();
				int maColIx = row.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				
				for(int colIx = minColIx;colIx<maColIx;colIx++){
					Cell cell = row.getCell(colIx);
					if(cell == null){
						continue;
					}
					rowList.add(getStringVal(cell));
				}
				result.add(rowList);
			}
			
		}
		return result;
	}
	
	
	@SuppressWarnings("deprecation")
	public static String getStringVal(Cell cell){
		switch(cell.getCellType()){
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue()?"TRUE":"FALSE";
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case Cell.CELL_TYPE_NUMERIC:
			cell.setCellType(Cell.CELL_TYPE_STRING);
			return cell.getStringCellValue();
		case Cell.CELL_TYPE_STRING:
			return cell.getStringCellValue();
			default:
				return "";
		}
	}
}
